package model.admin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import myutil.Connect;
import vo.BatchDetailsVo;
import vo.mixed.*;
import vo.*;
public class GetBatchListMo {
	public ArrayList<BatchDetailsVo> getBatchList() throws Exception{
		/*
		 * this method gets all the batches in the college for admin
		 * */	
			ArrayList<BatchDetailsVo> batch_list=new ArrayList<BatchDetailsVo>();
			Connect c=new Connect();
			ResultSet rs=c.getResult("select * from batch_details where status=1");
			try {
				while(rs.next()){
			
				BatchDetailsVo single_object=new BatchDetailsVo();
				single_object.setBatch_id(rs.getInt("batch_id"));
				single_object.setBatch_name(rs.getString("batch_name"));
				single_object.setDescription(rs.getString("description"));
				single_object.setStudent_count(rs.getInt("student_count"));
				
				batch_list.add(single_object);
				System.out.println("siva "+single_object.getBatch_name());
				
			}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
			return batch_list;
			
		}
		public ArrayList<DepartmentListVo> getDepartmentList(int batch_id, int degree_id) throws Exception{
			/*
			 * This method gets the departments present for one batch selected, or degree
			 */
			ArrayList<DepartmentListVo> batch_list=new ArrayList<DepartmentListVo>();
			Connect c=new Connect();
			String query="";
			if (degree_id==0 && batch_id==0)
				query="select d.department_id," +
						"	  d.department_name, " +
						"	  d.description " +
						"from department_details d where d.status=1;";
			else if (degree_id==0 && batch_id!=0)
					query="	select 	b.batch_name," +
									"d.department_id," +
									"d.department_name,		" +
									"d.description" +
							 " from department_details d," +
						  			"degree_department dd," +
						  			"batch_details b " +
						  	"where 	d.department_id	=	dd.department_id  " +
						  	  "and 	b.batch_id	    =	dd.batch_id  " +
						  	  "and 	dd.status=1 " +
						  	  "and  b.batch_id="+batch_id+" and b.status=1 and dd.status=1;";
			else if (batch_id==0 && degree_id!=0)
				    query="select  d.department_id," +
						  "		   d.department_name, " +
						  "		   d.description " +
						  "  from  department_details d " +
						  "  where status=1 and department_id in (select department_id" +
						  							"  from degree_department" +
						  							" where status=1 " +
						  							"   and degree_id	="+degree_id+");";
			else if (batch_id!=0 && degree_id!=0)
			query="select  d.department_id," +
				  "		   d.department_name, " +
				  "		   d.description " +
				  "  from  department_details d, " +
				  "	       batch_details b, " +
				  "degree_department dd" +
				  "  where b.batch_id	    =	dd.batch_id " +
				  "    and dd.department_id = d.department_id " +
				  "    and b.batch_id		=	"+batch_id+" " +
				  "	   and b.batch_id		= 	1" +
				  "    and dd.status		=	1" +
				  "    and d.status			=	1" +
				  "	   and d.department_id in (select department_id" +
				  							"  from degree_department" +
				  							" where status		=	1 " +
				  							"   and degree_id	=	"+degree_id+");"; 
			System.out.println(query);
			ResultSet rs=c.getResult(query);
			try {
			while(rs.next()){
				DepartmentListVo single_object=new DepartmentListVo();
				single_object.setBatch_id(batch_id);
				//single_object.setBatch_name(rs.getString("b.batch_name"));
				single_object.setDepartment_id(rs.getInt("d.department_id"));
				single_object.setDepartment_name(rs.getString("d.department_name"));
				single_object.setDescription(rs.getString("d.description"));
				System.out.println(single_object.getDepartment_name()+" Dept name ");
				 
//				single_object.setCourseList(getBatchCourseList(batch_id, single_object.getDepartment_id()));
				
				batch_list.add(single_object);
				
			}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				batch_list=null;
			}
			return batch_list;
	}
		public  ArrayList<CourseListVo> getBatchCourseList(int batch_id, int department_id,int professor_id) throws Exception{
			ArrayList<CourseListVo> batch_department_list = new ArrayList<CourseListVo>(); 
			Connect c=new Connect();
			String query="";
			if(professor_id==0 && batch_id==0 )
		  query="select c.course_name," +
		    	" c.course_code," +
		    	" c.course_id , " +
		    	" dd.department_name, " +
		    	" dd.department_id" +
           " from degree_department d, " +
           		" course_details c, " +
           		" department_details dd " +
	       "where d.department_id	=	dd.department_id " +
	       	 "and c.department_id	=	d.department_id " +
	       	 "and d.status			=	1 " +
	       	 "and c.status			=	1 " +
	       	 "and dd.status			=	1 " +
	       	 "and c.department_id		=	"+department_id+";";
			else if(professor_id==0 && department_id==0)				
			query="select c.course_name," +
		    	" c.course_code," +
		    	" c.course_id , " +
		    	" dd.department_name, " +
		    	" dd.department_id" +
           " from degree_department d, " +
           		" course_details c, " +
           		" department_details dd " +
	       "where d.department_id	=	dd.department_id " +
	       	 "and c.department_id	=	d.department_id " +
	       	 "and d.status			=	1 " +
	       	 "and c.status			=	1 " +
	       	 "and dd.status			=	1 " +
	       	 "and d.batch_id		=	"+batch_id+";";
	       	 else if (professor_id==0 &&department_id!=0 && batch_id!=0)
	query="select c.course_name," +
		    	" c.course_code," +
		    	" c.course_id , " +
		    	" dd.department_name, " +
		    	" dd.department_id" +
           " from degree_department d, " +
           		" course_details c, " +
           		" department_details dd " +
	       "where d.department_id	=	dd.department_id " +
	       	 "and c.department_id	=	d.department_id" +"  "+
	       	 "and d.status			=	1 " +
	       	 "and c.status			=	1 " +
	       	 "and dd.status			=	1 " +
	       	 "and d.department_id	=	"+department_id+"  "+
	       	 "and d.batch_id		=	"+batch_id+";";
	       	 else if (professor_id!=0 &&batch_id!=0)
	       		query="select c.course_name," +
		    	" c.course_code," +
		    	" c.course_id , " +
		    	" dd.department_name, " +
		    	" dd.department_id" +
           " from degree_department d, " +
           		" course_details c, " +
           		" department_details dd " +
	       "where d.department_id	=	dd.department_id " +
	       	 "and c.department_id	=	d.department_id" +"  "+
	       	 "and d.status			=	1 " +
	       	 "and c.status			=	1 " +
	       	 "and dd.status			=	1 " +
	       	 "and d.batch_id		=	"+batch_id+" " +
	       	 "and c.course_id 		in (select course_id " +
	       	 							 "from course_professor" +
	       	 						   " where professor_id_1	=	"+professor_id+" " +
	       	 						   	   "or professor_id_2	=	"+professor_id+" " +
	       	 						   	   "or professor_id_3	=	"+professor_id+" " +
	       	 						   	   "or professor_id_4 	=	"+professor_id+" );";
			System.out.println(query);

			ResultSet rs= null;
			try {
				rs = c.getResult(query);		
				while(rs.next()){
					CourseListVo single_object = new CourseListVo();
					single_object.setCourse_code(rs.getString("c.course_code"));
					single_object.setCourse_id(rs.getInt("c.course_id"));
					single_object.setDepartment_id(rs.getInt("dd.department_id"));
					single_object.setDepartment_name(rs.getString("dd.department_name"));
					single_object.setCourse_name(rs.getString("c.course_name"));
					
					batch_department_list.add(single_object);
					
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return batch_department_list;
		}
		public ArrayList<ProfessorListVo> getBatchProfessorList(int batch_id)
		{
			ArrayList<ProfessorListVo> batch_professor_list= new ArrayList<ProfessorListVo>();
			
			String query="select cs.staff_name," +
								 "cs.staff_id," +
								 "pw.patent," +
								 "pw.research," +
								 "pw.project" +
						  " from college_staff cs," +
						  	     "professor_work pw  " +
						  "where cs.status=1 " +
						  "  and cs.staff_id	=	pw.professor_id " +
						  "  and cs.staff_id in (select pd.professor_id" +
						  						"  from professor_department pd," +
						  								"degree_department dd " +
						  						 "where dd.department_id	=	pd.department_id" +
						  						 "  and dd.status			=	1  " +
						  						   "and batch_id			=	"+batch_id+");";
			Connect c=new Connect();
			ResultSet rs= null;
			try {
				rs = c.getResult(query);		
				while(rs.next()){
					
					ProfessorListVo single_object = new ProfessorListVo();
					single_object.setPatent(rs.getString("pw.patent"));
					single_object.setStaff_id(rs.getInt("cs.staff_id"));
					single_object.setProject(rs.getString("pw.project"));
					single_object.setResearch(rs.getString("pw.research"));
					single_object.setStaff_name(rs.getString("cs.staff_name"));
					
					batch_professor_list.add(single_object);
				}
			}catch (Exception e) {
				// TODO: handle exception
			}
			return batch_professor_list;
		}

		public ArrayList<StudentListVo> getBatchStudentList(int batch_id, int degree_id)
		{
ArrayList<StudentListVo> batch_student_list= new ArrayList<StudentListVo>();
String query="";
	if(	batch_id	==	0)
		query="select sd.student_name," +
					" sd.student_id, " +
					" sd.city," +
					" sd.highest_degree," +
					" sd.gender," +
					" sd.years_of_experience," +
					" sd.registration_number" +
			   " from student_details sd where sd.status=1; ";
	else 
	query="select sd.student_name," +
					" sd.student_id, " +
					" sd.city," +
					" sd.highest_degree," +
					" sd.gender," +
					" sd.years_of_experience," +
					" sd.registration_number" +
			  " from student_details sd " +
			  "where status	=	1" +
			  " and student_id in (select sb.student_id " +
			                         "from student_batch sb," +
			                               "batch_details bd " +
			                         "where bd.batch_id	=	sb.batch_id " +
			                         "  and sb.batch_id	=	"+batch_id+");";
			Connect c=new Connect();
			ResultSet rs= null;
			try {
				rs = c.getResult(query);		
				while(rs.next()){
					
					StudentListVo single_object = new StudentListVo();
					single_object.setStudent_id(rs.getInt("sd.student_id"));
					single_object.setStudent_name(rs.getString("sd.student_name"));
					single_object.setCity(rs.getString("sd.city"));
					single_object.setGender(rs.getString("sd.gender"));
					single_object.setHighest_degree(rs.getString("sd.highest_degree"));
					single_object.setWork_experience(rs.getInt("sd.years_of_experience"));
					single_object.setRegistration_number(rs.getString("sd.registration_number"));
					System.out.print("Studnet names"+single_object.getStudent_name());

					batch_student_list.add(single_object);
				}
			}catch (Exception e) {
				// TODO: handle exception
				System.out.print("error+"+e.getMessage());
				
			}
			return batch_student_list;
			
		}
		public ArrayList<DegreeListVo> getBatchDegreeList(int batch_id) {
			
ArrayList<DegreeListVo> batch_degree_list= new ArrayList<DegreeListVo>();
String query="";			
if (batch_id==0)
			query="select  dm.degree_id," +
							      "bd.batch_name," +
							      "dm.batch_id, " +
							      "dd.degree_name " +
						   "from  degree_management dm," +
						   		  "degree_details dd," +
						   		  "batch_details bd " +
						   "where bd.batch_id	=	dm.batch_id" +
						    " and dd.degree_id	=	dm.degree_id" +
						    " and dd.status		=	1 " +
						    " and dd.status		=	1;";
else
			 query="select  dm.degree_id," +
							      "bd.batch_name," +
							      "dm.batch_id, " +
							      "dd.degree_name " +
						   "from  degree_management dm," +
						   		  "degree_details dd," +
						   		  "batch_details bd " +
						   "where bd.batch_id	=	dm.batch_id" +
						    " and dd.degree_id	=	dm.degree_id" +
						    " and dd.status		=	1" +
						    " and bd.status		=	1" +
						    " and bd.batch_id	=	"+batch_id+";";
			Connect c=new Connect();
			System.out.println(query);
			ResultSet rs= null;
			try {
				rs = c.getResult(query);		
				while(rs.next()){
					
					DegreeListVo single_object = new DegreeListVo();
					single_object.setBatch_id(rs.getInt("dm.batch_id"));
					single_object.setBatch_name(rs.getString("bd.batch_name"));
					single_object.setDegree_name(rs.getString("dd.degree_name"));
					single_object.setDegree_id(rs.getInt("dm.degree_id"));
					
					batch_degree_list.add(single_object);
				}
			}catch (Exception e) {
				// TODO: handle exception
				System.out.print("error+"+e.getMessage());
				
			}
			return batch_degree_list;
					}
		public ArrayList<StudentListVo> getDegreeStudentList(int degree_id, int batch_id)
		{
			ArrayList<StudentListVo> student_list= new ArrayList<StudentListVo>();
		
			String query="";
			if (batch_id==0)
			query="select * " +
					     "  from " +
					     "       student_details " +
					     " where student_id in (select sb.student_id " +
					                            " from degree_department dd," +
					                            "      student_batch sb " +
					                            "where dd.batch_id	=	sb.batch_id " +
					                            "  and dd.status=1 " +
					                            "  and dd.degree_id="+degree_id+");";
			else
				query="select * " +
					     "  from " +
					     "       student_details " +
					     " where student_id in (select sb.student_id " +
					                            " from degree_department dd," +
					                            "      student_batch sb " +
					                            "where dd.batch_id	=	sb.batch_id " +
					                            "  and dd.status	=	1 " +
					                            "  and sb.batch_id="+batch_id+"" +
					                            "  and dd.degree_id="+degree_id+");";
			Connect c=new Connect();
			ResultSet rs= null;
			try {
				rs = c.getResult(query);	
				System.out.println(query+"  qury ");
				while(rs.next()){
					
					StudentListVo single_object = new StudentListVo();
					single_object.setStudent_id(rs.getInt("student_id"));
					single_object.setBatch_id(batch_id);
					single_object.setDegree_id(degree_id);
					single_object.setCity(rs.getString("city"));
					single_object.setGender(rs.getString("gender"));
					single_object.setWork_experience(rs.getInt("years_of_experience"));
					single_object.setStudent_name(rs.getString("student_name"));
					single_object.setHighest_degree(rs.getString("highest_degree"));
					System.out.println(single_object.getStudent_id()+"\n s id");
					student_list.add(single_object);
				}
			}catch (Exception e) {
				// TODO: handle exception
				System.out.print("error+"+e.getMessage());
				student_list	=	null;
				
			}
			return student_list;
		}
			
		
}
